import pymysql
import time
import logging

from logging import handlers

class Logger(object):
    level_relations = {
        'debug':logging.DEBUG,
        'info':logging.INFO,
        'warning':logging.WARNING,
        'error':logging.ERROR,
        'crit':logging.CRITICAL
    }#日志级别关系映射

    def __init__(self,filename,level='info',when='D',backCount=3,fmt='%(asctime)s - %(pathname)s[line:%(lineno)d] - %(levelname)s: %(message)s'):
        self.logger = logging.getLogger(filename)
        format_str = logging.Formatter(fmt)#设置日志格式
        self.logger.setLevel(self.level_relations.get(level))#设置日志级别
        # sh = logging.StreamHandler()#往屏幕上输出
        # sh.setFormatter(format_str) #设置屏幕上显示的格式
        if not self.logger.handlers: # 判断对象是否重复实例化,造成同一信息逐一递增输出
            th = handlers.TimedRotatingFileHandler(filename=filename,when=when,backupCount=backCount,encoding='utf-8')#往文件里写入#指定间隔时间自动生成文件的处理器
            #实例化TimedRotatingFileHandler
            #interval是时间间隔，backupCount是备份文件的个数，如果超过这个个数，就会自动删除，when是间隔的时间单位，单位有以下几种：
            # S 秒
            # M 分
            # H 小时、
            # D 天、
            # W 每星期（interval==0时代表星期一）
            # midnight 每天凌晨
            th.setFormatter(format_str)#设置文件里写入的格式
            # self.logger.addHandler(sh) #把对象加到logger里
            self.logger.addHandler(th)
            # self.logger.removeHandler(sh)
            # self.logger.removeHandler(th)

#获取联接
db = pymysql.Connect(
    host='localhost',
    port=3306,
    user='root',
    password='DRsXT5ZJ6Oi55LPQ',
    db='xiaoyuan',
    charset='utf8'
)
db2 = pymysql.Connect(
    host='localhost',
    port=3306,
    user='root',
    password='DRsXT5ZJ6Oi55LPQ',
    db='xiaoyuan',
    charset='utf8'
)

#获取操作句柄
cursor = db.cursor()

# SQL查询
sql = "SELECT * FROM cmf_mask_delivery WHERE status = %s OR status = %s"
Logger('time.log', level='debug').logger.debug("开始执行计划任务")
try:
    cursor.execute(sql,(1,2))
    result = cursor.fetchall()
    for row in result:
        # 七天自动确认订单信息
        # if  int(time.time() - 7 * 24 * 60 * 60) > int(row[9]) :
        # 一分钟一次扫描 一分钟自动确认订单信息
        if  int(time.time()) > int(row[9]) :
            try:
                cursor2 = db2.cursor()
                sql2 = "UPDATE cmf_mask_delivery SET status = 3 WHERE id = %s"
                cursor2.execute(sql2,(row[0]))
                db2.commit()
                Logger('all.log', level='debug').logger.debug("将订单id为:{0}-改变为已收货".format(row[0]))
                time.sleep(0.1)
            except:
                Logger('error.log', level='error').logger.error("订单id为:{0}-修改失败".format(row[0]))
                db2.rollback()

except:
    Logger('run.log', level='error').logger.error("无法连接数据库")
    # print("Error: unable to fetch data")
Logger('time.log', level='debug').logger.debug("结束计划任务")
db.close()

